<?php

include_once './Utils.php';
include_once './log4php/Logger.php';
include_once './User.php';

date_default_timezone_set('UTC');
Logger::configure('./log4j.properties');

/**
 * Call the MySQLConnect::setConnectionConfig(..) in order to configure
 * the MySQL DB connection config, before do any request to the DB.
 */
define('ERROR_MYSQL_CONFIG_NOT_INITIALIZED', 10001);


/**
 * The class DBException represents DB related exceptions/errors.
 * Just simple extends the Exception class.
 */
class DBException extends Exception {
}


/**
 * The class responsible for the MySQL DB connection.
 *
 * UT: MySQLConnectTest
 * 
 * @author lacci
 */
class MySQLConnect {
    public static $log;

    /**
     *
     * @var string MySQL DB server host name + port, e.g. "127.0.0.1:3306"
     */
    private static $mysql_host;

    /**
     *
     * @var string MySQL DB login user name
     */
    private static $mysql_user;

    /**
     *
     * @var string MySQL DB login password
     */
    private static $mysql_pass;

    /**
     *
     * @var string MySQL DB name
     */
    private static $mysql_db;

    /**
     * Call this function before any other functions of this class!!!
     * Sets the db connection configuration!!!
     * 
     * @param string $mysql_host
     * @param string $mysql_user
     * @param string $mysql_pass
     * @param string $mysql_db
     */
    public static function setConnectionConfig($mysql_host, $mysql_user, $mysql_pass, $mysql_db) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);

        self::$mysql_host = $mysql_host;
        self::$mysql_user = $mysql_user;
        self::$mysql_pass = $mysql_pass;
        self::$mysql_db = $mysql_db;

        self::$log->debug("HOST: " . self::$mysql_host . ", USER: " . self::$mysql_user . ", PASS: " . self::$mysql_pass . ", DB: " . self::$mysql_db);
        self::$log->debug("[ END ] " . __METHOD__);
    }

    /**
     * Makes a db connection and returns the mysqli object.
     * Use the returned object to execute db queries.
     * 
     * @return mysqli 
     */
    private static function createDBConnection() {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);

        // check the mandatory config parameters
        if (is_null(self::$mysql_host)
                || is_null(self::$mysql_user)
                || is_null(self::$mysql_pass)
                || is_null(self::$mysql_db)) {
            self::$log->error(
                    "ERROR: [" . ERROR_MYSQL_CONFIG_NOT_INITIALIZED . "] "
                    . "Connection config uninitialized! First call setConnectionConfig()!");
            self::$log->debug("[ END ] " . __METHOD__);
            throw new DBException(
                    "Connection config uninitialized! First call setConnectionConfig()!",
                    ERROR_MYSQL_CONFIG_NOT_INITIALIZED);
        }

        // create mysqli object / make connection to the db
        $mysqli = new mysqli(self::$mysql_host, self::$mysql_user, self::$mysql_pass, self::$mysql_db);
        // check is the db connection was succesfull
        if (mysqli_connect_errno ()) {
            self::$log->error("ERROR: [" . mysqli_connect_errno() . "] " . mysqli_connect_error());
            self::$log->debug("[ END ] " . __METHOD__);
            throw new DBException(mysqli_connect_error(), mysqli_connect_errno());
        }

        self::$log->debug('Connected successfully');
        self::$log->debug("[ END ] " . __METHOD__);
        
        return $mysqli;
    }

    /**
     * Builds up the SQL query used to check which users are already in the DB.
     * Used by filterExistingUsers(..).
     * 
     * @param array $users array of uid-User pairs
     * @return string SQL query
     */
    private static function buildExistingUsersQuery($users) {
        $query = "SELECT `uid` FROM `user` WHERE `uid` IN (";
        $query .= implode(", ", array_keys($users));
        $query .= ");";
        return $query;
    }


    /**
     * Removes from the array the users already present in the DB.
     *
     * @param array $userArray associative array of uid-User object pairs
     * @return array the filtered out input array
     *
     */
    public static function filterExistingUsers($userArray) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);

        $mysqli = self::createDBConnection();
        $query = self::buildExistingUsersQuery($userArray);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);   // do the query
        // check the result
        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        self::$log->debug("Result rows: " . mysqli_num_rows($result));
        $filteredArray = $userArray;
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $uid = $resultRow['uid'];
            unset($filteredArray[$uid]);
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);

        self::$log->debug("[ END ] " . __METHOD__);
        return $filteredArray;
    }

    /**
     * Builds up the SQL INSERT query for user insertion.
     *
     * @param array $users array of users to be added to the DB, MUST BE NOT EMPTY!!!
     */
    private static function buildInsertUsersQuery($users) {
        $query = "INSERT INTO `user` (`uid`, `name`, `pic_square`, `pic`) VALUES ";
        $userSubPartArray = array();

        foreach ($users as $user) {
            $pic_square = $user->fb_pic_url;
            $pic_square_sub = ($pic_square!=NULL ? "'$pic_square'" : "NULL");
            $pic = $user->getExtraData('pic');
            $pic_sub = ($pic!=NULL ? "'$pic'" : "NULL");
            
            $userSubPartArray[] = ("(" . $user->getUid() . ", '" . $user->getName() . "', " . $pic_square_sub . ", " . $pic_sub . ")");
        }

        $query .= implode(', ', $userSubPartArray);
        $query .= ";";

        return $query;
    }

    /**
     * Inserts the array of users to the DB. In case of duplicate uid error is thrown and the operation is broken.
     *
     * @param array $userArray array of uid-User pairs to be inserted
     * @return integer number of inserted records
     * @throws DBException in case of any DB related problem
     */
    public static function insertUsers($userArray) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);

        if (empty ($userArray)) {
            self::$log->debug("Nothing to insert.");
            self::$log->debug("[ END ] " . __METHOD__);
            return 0;
        }

        $mysqli = self::createDBConnection();
        $query = self::buildInsertUsersQuery($userArray);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: '$result'");

        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $affectedRows = $mysqli->affected_rows;
        self::closeDBCOnnection($mysqli);
        self::$log->debug("SUCCESS! Affected rows: $affectedRows");
        self::$log->debug("[ END ] " . __METHOD__);
        return $affectedRows;
    }

    /**
     * Marks the specified users as outdate, 
     * so their user data will be udpated as soon as possible.
     * 
     * @param array $uid_array array of uid
     */
    public static function setUserAsOutdated($uid_array) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);

        // set up MySQL connection
        self::setConnectionConfig(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);
        
        $mysqli = self::createDBConnection();
        $query = "UPDATE `user` SET `update`=TRUE WHERE `uid` IN (" . implode(",", $uid_array) . ");";
        self::$log->debug("Query: $query");
        $result = $mysqli->query($query);
        self::$log->debug("Result: $result");

        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        self::$log->debug("Affected rows is: " . $mysqli->affected_rows);
        self::closeDBCOnnection($mysqli);
        self::$log->debug("[ END ] " . __METHOD__);
    }

    private static function buildOutdatedUsersQuery($users) {
        $query = "SELECT `uid` FROM `user` WHERE `update`=TRUE AND `uid` IN (";
        $query .= implode(", ", array_keys($users));
        $query .= ");";
        return $query;
    }

    public static function filterOutdatedUsers($userArray) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);

        $mysqli = self::createDBConnection();
        $query = self::buildOutdatedUsersQuery($userArray);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);   // do the query
        // check the result
        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        self::$log->debug("Result rows: " . mysqli_num_rows($result));
        $filteredArray = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $uid = $resultRow['uid'];
            $filteredArray[$uid] = $userArray[$uid];
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);

        self::$log->debug("[ END ] " . __METHOD__);
        return $filteredArray;
    }

    /**
     *
     * @param integer $uid MANDATORY the uid of the user to be updated
     * @param string $name MANDATORY the new name of the user
     * @param string $pic_square OPTIONAL the URL to the profile pic_square picture
     * @param string $pic OPTIONAL the URL to the profile pic picture
     * @return integer the uid of the updated user
     */
    private static function buildUpdateUserQuery($uid, $name, $pic_square, $pic) {
        $query = "UPDATE `user` SET `name`='$name'";
        if ($pic_square!=NULL) {
            $query .= ", `pic_square`='$pic_square'";
        }
        if ($pic!=NULL) {
            $query .= ", `pic`='$pic'";
        }
        $query .= " WHERE `uid`=$uid;";
        return $query;
    }

    public static function updateUser($uid, $name, $pic_square = NULL, $pic_big = NULL) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = self::buildUpdateUserQuery($uid, $name, $pic_square, $pic_big);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);

        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        self::$log->debug("SUCCESS. Result = $result");
        self::closeDBCOnnection($mysqli);
        self::$log->debug("[ END ] " . __METHOD__);
    }

    /**
     *
     * @param array $dbFriendsArray
     * @param array $fbFriendsArray
     */
    public static function updateFriends($uid, $dbFriendsArray, $fbFriendsArray) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);

        $toBeAdded = array_diff($fbFriendsArray, $dbFriendsArray);
        $toBeRemoved = array_diff($dbFriendsArray, $fbFriendsArray);

        if (!empty ($toBeAdded)) {
            MySQLConnect::insertFriends($uid, $toBeAdded);
        }

        if (!empty ($toBeRemoved)) {
            MySQLConnect::removeFriends($uid, $toBeRemoved);
        }
        self::$log->debug("[ END ] " . __METHOD__);
    }

    /**
     *
     * Returns the array of friends uid for the given user (uid)
     * @param integer $uid the user whose friends will be discovered
     * @return array array of fid for the given uid
     */
    public static function getFriends($uid) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);

        $mysqli = self::createDBConnection();
        $query = "SELECT `uid2` FROM `friend` WHERE `uid1`=$uid;";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow['uid2'];
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);

        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }

    /**
     *
     * Inserts the friendship between the logged in user and his/her friends to the DB.
     * 
     * @param User $user the logged in user
     * @param array $fidArray array of friends' uid
     */
    public static function insertFriends($uid, $fidArray) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);

        $query = "INSERT IGNORE INTO `friend` (`uid1`, `uid2`) VALUES ";
        $first = TRUE;
        foreach ($fidArray as $index => $fid) {
            if ($first) {
                $first = FALSE;
            }
            else {
                $query .= ", ";
            }

            $query .= "($uid, $fid), ($fid, $uid)";
        }
        $query .= ";";
		//echo $query;
        $mysqli = self::createDBConnection();
        self::$log->debug("Query: $query");
        $result = $mysqli->query($query);
        self::$log->debug("Result: $result");

        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        self::$log->debug("Affected rows is: " . $mysqli->affected_rows);
        self::closeDBCOnnection($mysqli);
        self::$log->debug("[ END ] " . __METHOD__);
    }

    public static function removeFriends($uid, $fidArray) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);

        $query = "DELETE FROM `friend` WHERE ";

        $first = TRUE;
        foreach ($fidArray as $index => $fid) {
            if ($first) {
                $first = FALSE;
            }
            else {
                $query .= " OR ";
            }

            $query .= "(`uid1`=$uid AND `uid2`=$fid) OR (`uid1`=$fid AND `uid2`=$uid)";
        }
        $query .= ";";

        $mysqli = self::createDBConnection();
        self::$log->debug("Query: $query");
        $result = $mysqli->query($query);
        self::$log->debug("Result: $result");

        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        self::$log->debug("Affected rows is: " . $mysqli->affected_rows);

        $expected_records = 2*count($fidArray);
        if ($mysqli->affected_rows != $expected_records) {
            self::$log->error("Expected number of removed records is $expected_records, but were removed " . $mysqli->affected_rows);
        }
        self::closeDBCOnnection($mysqli);
        self::$log->debug("[ END ] " . __METHOD__);
    }


    /**
     *
     * @param mysqli $mysqli
     * @param array $users array of users to be added to the DB, MUST BE NOT EMPTY!!!
     */
    private static function buildInsertLocationsQuery($mysqli, $userArray) {
        $query = "INSERT IGNORE INTO `location` (`uid`, `g`, `address`, `note`, `timestamp`) VALUES ";

        $first = TRUE;
        foreach ($userArray as $user) {
            if (!isset ($user->fb_location)) {
                continue;
            }
            
            if ($first) {
                $first = FALSE;
            }
            else {
                $query .= ", ";
            }

            $lon = $user->fb_location->lon;
            $lat = $user->fb_location->lat;
            $address = $mysqli->real_escape_string($user->fb_location->address);
            $note = $mysqli->real_escape_string($user->fb_location->data);
            $timestamp = ($user->fb_location->timestamp!=NULL ? ("'" . date("Y-m-d H:i:s", $user->fb_location->timestamp) . "'") : "NOW()");
            $uid = $user->fb_uid;
            $g = "GeomFromText('POINT($lon $lat)')";

            $query .= "($uid, $g, '$address', '$note', $timestamp)";
        }

        $query .= ";";

        return $query;
    }

    /**
     * Inserts the location of the Users passed in.
     *
     * @param array $userArray array of User objects (with Location field)
     * @return <type>
     */
    public static function insertLocations($userArray) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = self::buildInsertLocationsQuery($mysqli, $userArray);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: '$result'");

        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        self::$log->debug("SUCCESS! Affected rows: " . $mysqli->affected_rows);
        self::closeDBCOnnection($mysqli);
        self::$log->debug("[ END ] " . __METHOD__);
    }

    /**
     *
     * @param mysqli $mysqli
     * @param <type> $uid
     * @param <type> $lon
     * @param <type> $lat
     * @param <type> $address
     * @param <type> $note
     * @return <type>
     */
    private static function buildInsertLocationQuery($mysqli, $uid, $lon, $lat, $address, $note) {
        $query = "INSERT INTO `location` (`uid`, `g`, `address`, `note`, `timestamp`) VALUES ";

        $address = $mysqli->real_escape_string($address);
        $note = $mysqli->real_escape_string($note);
        $timestamp = "NOW()";
        $g = "GeomFromText('POINT($lon $lat)')";

        $query .= "($uid, $g, '$address', '$note', $timestamp);";
        
        return $query;
    }

    /**
     * Inserts the location of the Users passed in.
     *
     * @param array $userArray array of User objects (with Location field)
     * @return <type>
     */
    public static function insertLocation($uid, $lon, $lat, $address, $note) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = self::buildInsertLocationQuery($mysqli, $uid, $lon, $lat, $address, $note);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: '$result'");

        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        self::$log->debug("SUCCESS! Affected rows: " . $mysqli->affected_rows);
        self::closeDBCOnnection($mysqli);
        self::$log->debug("[ END ] " . __METHOD__);
    }

    /**
     *
     * @param int $uid
     * @return array
     */
    public static function getAllLocations($uid) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_locations($uid);";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            $error = $mysqli->error;
            $errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }
    
    /**
     * Builds insert message query.
     * 
     * @param $sid sender ID
     * @param $rid receiver ID
     * @param $message message content
     * @param $lat message latitude
     * @param $lon message longitude
     * @param $address message address
     * @return query
     */
    public static function buildInsertMessageQuery($mysqli, $sid, $rid, $message, $lat, $lon, $address) {
		$query = "INSERT INTO `message` (`sid`, `rid`, `message`, `timestamp`, `g`, `address`) VALUES ";

        $message = $mysqli->real_escape_string($message);
        $timestamp = "UNIX_TIMESTAMP()";
        
        $g = "NULL";
        if (($lat != NULL) && ($lon != NULL))
			$g = "GeomFromText('POINT($lon $lat)')";

        $query .= "($sid, $rid, '$message', $timestamp, $g, '$address');";

        return $query;
	}
	
	/**
     * Inserts the message.
     *
     * @param $sid sender ID
     * @param $rid receiver ID
     * @param $message message content
     * @param $lat message latitude
     * @param $lon message longitude
     * @param $address message address
     * @return affected rows
     */
    public static function insertMessage($sid, $rid, $message, $lat, $lon, $address) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = self::buildInsertMessageQuery($mysqli, $sid, $rid, $message, $lat, $lon, $address);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: '$result'");

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $affectedRows = $mysqli->affected_rows;
        self::closeDBCOnnection($mysqli);
        self::$log->debug("SUCCESS! Affected rows: $affectedRows");
        self::$log->debug("[ END ] " . __METHOD__);
        return $affectedRows;
    }
	
	/**
     * Get received messages
     * 
     * @param int $uid
     * @return array
     */
    public static function getReceivedMessages($uid) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_received_messages($uid);";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
			$error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }
	
	/**
     * Get sent messages
     * 
     * @param int $uid
     * @return array
     */
    public static function getSentMessages($uid) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_sent_messages($uid);";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }

	/**
     * Builds remove message query.
     * 
     * @param $mid message ID
     * @param $uid user ID
     * @return query
     * 
     * Removes message if user is message sender or receiver
     */
    public static function buildRemoveMessageQuery($mysqli, $mid, $uid) {
		$query = "DELETE FROM message WHERE ";
		$query .= "(message.rid = $uid OR ";
		$query .= "message.sid = $uid) AND ";
		$query .= "message.mid = $mid;";
        
        return $query;
	}

	/**
     * Removes the message.
     *
     * @param $mid message ID
     * @param $uid user ID
     */
    public static function removeMessage($mid, $uid) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = self::buildRemoveMessageQuery($mysqli, $mid, $uid);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: '$result'");

        if (!$result) {
			$error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }
        self::$log->debug("SUCCESS! Affected rows: " . $mysqli->affected_rows);
        self::closeDBCOnnection($mysqli);
        self::$log->debug("[ END ] " . __METHOD__);
    }
    
    /**
     * Inserts tag to the user.
     * 
     * @param $uid user ID
     * @param $tagName tag name
     */
    public static function insertTag($uid, $tagName) {
		self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $tagId = self::insertOrUpdateTag($tagName);
        self::insertUserTag($uid, $tagId);
	}
	
	/**
	 * Builds insert or update query for tag.
	 * 
	 * @param $tagName tag name
	 * @return $query
	 */ 
	public static function buildInsertOrUpdateTag($tagName) {
		$query = "INSERT INTO tag (name) ";
		$query .= "VALUES('$tagName') ";
		$query .= "ON DUPLICATE KEY UPDATE count = count + 1";
        
        return $query;
	}
	
	public static function insertOrUpdateTag($tagName) {
		self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = self::buildInsertOrUpdateTag($tagName);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: '$result'");

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

		$tagIdQuery = self::executeSelect("SELECT tid FROM tag WHERE 
			name = '$tagName' LIMIT 1");
			
		if ($tagIdQuery != NULL)
			$tagId = $tagIdQuery[0]['tid'];

        self::closeDBCOnnection($mysqli);
        self::$log->debug("[ END ] " . __METHOD__);
        return $tagId;
	}

	/**
	 * Builds insert user tag query.
	 * 
	 * @param $uid user ID
	 * @param $tid tag ID
	 * @return query
	 */ 
	public static function buildInsertUserTag($uid, $tid) {
		$query = "INSERT INTO usertag (uid, tid) ";
		$query .= "VALUES($uid, $tid);";
        
        return $query;
	}
	
	/**
	 * Inserts user ID and tag ID to DB.
	 * 
	 * @param $uid user ID
	 * @param $tid tag ID
	 * @return affected rows
	 */ 
	public static function insertUserTag($uid, $tid) {
		self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = self::buildInsertUserTag($uid, $tid);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: '$result'");

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

		$affectedRows = $mysqli->affected_rows;
        self::closeDBCOnnection($mysqli);
        self::$log->debug("SUCCESS! Affected rows: $affectedRows");
        self::$log->debug("[ END ] " . __METHOD__);
        return $affectedRows;
	}

	/**
	 * Builds remove user tag query.
	 * 
	 * @param $uid user ID
	 * @param $tid tag ID
	 * @return query
	 */ 
	public static function buildRemoveUserTagQuery($uid, $tid) {
		$query = "DELETE FROM usertag WHERE ";
		$query .= "uid = $uid AND tid = $tid;";
        
        return $query;
	}
	
	/**
	 * Removes user ID, tag ID pair from DB.
	 * 
	 * @param $uid user ID
	 * @param $tid tag ID
	 * @return affected rows
	 */ 
	public static function removeTag($uid, $tid) {
		self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = self::buildRemoveUserTagQuery($uid, $tid);
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: '$result'");

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

		$affectedRows = $mysqli->affected_rows;
        self::closeDBCOnnection($mysqli);
        self::$log->debug("SUCCESS! Affected rows: $affectedRows");
        self::$log->debug("[ END ] " . __METHOD__);
        return $affectedRows;
	}

    public static function executeSelect($selectQuery) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        self::$log->debug("Query: '$selectQuery'");
        $result = $mysqli->query($selectQuery);

        if (!$result) {
            self::$log->error("ERROR: [" . $mysqli->errno . "] " . $mysqli->error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($mysqli->error, $mysqli->errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);

        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }

	/**
     * Get user tags
     * 
     * @param int $uid
     * @return array
     */
    public static function getUserTags($uid) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_user_tags($uid);";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }

	/**
     * Get popular tags
     * 
     * @return array
     */
    public static function getPopularTags() {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_popular_tags();";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }
	
	/**
     * Returns friends with same tags as user.
     * 
     * @param int $uid
     * @return array
     */
    public static function getFriendsWithSameTags($uid) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_friends_with_same_tags($uid);";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            self::$log->error("ERROR: [" . $mysqli->errno . "] " . $mysqli->error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($mysqli->error, $mysqli->errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }
    
    /**
     * Returns others with same tags as user.
     * 
     * @param int $uid
     * @return array
     */
    public static function getOthersWithSameTags($uid) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_others_with_same_tags($uid);";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            self::$log->error("ERROR: [" . $mysqli->errno . "] " . $mysqli->error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($mysqli->error, $mysqli->errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }
    
    /**
     * Get friends from the area
     * 
     * @return array
     */
	public static function getFriendsFromArea($uid, $distance) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_friends_from_area($uid, $distance);";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }
    
    /**
     * Get others from the area
     * 
     * @return array
     */
	public static function getOthersFromArea($uid, $distance) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_others_from_area($uid, $distance);";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }

	/**
     * Get friends with tag
     * 
     * @return array
     */
	public static function getFriendsWithTag($uid, $distance, $tag) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_friends_with_tag($uid, $distance, '$tag');";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }

	/**
     * Get others with tag
     * 
     * @return array
     */
	public static function getOthersWithTag($uid, $distance, $tag) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        
        $mysqli = self::createDBConnection();
        $query = "CALL get_others_with_tag($uid, $distance, '$tag');";
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: " . Utils::toString($result));

        if (!$result) {
            $error = $mysqli->error;
			$errno = $mysqli->errno;
            self::$log->error("ERROR: [" . $errno . "] " . $error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($error, $errno);
        }

        $resultData = array();
        while ($resultRow = mysqli_fetch_assoc($result)) {
            $resultData[] = $resultRow;
        }

        mysqli_free_result($result);
        self::closeDBCOnnection($mysqli);
        
        self::$log->debug("[ END ] " . __METHOD__);
        return $resultData;
    }

    /**
     *
     * @param string $query
     * @return int number of affected rows
     */
    public static function executeInsertOrUpdateOrDelete($query) {
        self::$log = Logger::getLogger(__CLASS__);
        self::$log->debug("[START] " . __METHOD__);
        $mysqli = self::createDBConnection();
        self::$log->debug("Query: '$query'");
        $result = $mysqli->query($query);
        self::$log->debug("Result: '$result'");

        if (!$result) {
            self::$log->error("ERROR: [" . $mysqli->errno . "] " . $mysqli->error);
            self::$log->debug("[ END ] " . __METHOD__);
            self::closeDBCOnnection($mysqli);
            throw new DBException($mysqli->error, $mysqli->errno);
        }

        $affectedRows = $mysqli->affected_rows;
        self::closeDBCOnnection($mysqli);

        self::$log->debug("SUCCESS! Affected rows: $affectedRows");
        self::$log->debug("[ END ] " . __METHOD__);        
        return $affectedRows;
    }

    /**
     * Closes the connection with the db.
     * Don't forget to call this function when the db connection is not neccesary.
     * 
     *
     * @param mysqli $mysqli
     */
    private static function closeDBCOnnection($mysqli) {
        mysqli_close($mysqli);
    }
}

?>
